package domain.web.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import domain.web.dao.StoreDao;
import domain.web.model.Store;
import domain.web.model.StoreView;

@Repository("storeDao")
public class StoreDaoImpl extends JdbcDaoSupport implements StoreDao {

	@Autowired
	private DataSource dataSource;

	@PostConstruct
	void init() {
		setDataSource(dataSource);
	}

	@Override
	public long insert(final Store store) {
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		final PreparedStatementCreator psc = new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement pstmt = con.prepareStatement(
						"INSERT INTO Stores(Name, Website) " + "VALUES(?,?)",
						Statement.RETURN_GENERATED_KEYS);
				pstmt.setString(1, store.getName());
				pstmt.setString(2, store.getWebsite());
				return pstmt;
			}
		};
		getJdbcTemplate().update(psc, keyHolder);
		return keyHolder.getKey().longValue();
	}

	@Override
	public void update(Store store) {
		String sql = "UPDATE Stores SET Name = ?, Website = ? WHERE Id = ?";
		getJdbcTemplate().update(sql, store.getName(), store.getWebsite(),
				store.getId());
	}

	@Override
	public void delete(long id) {
		String sql = "DELETE FROM Stores WHERE Id = ?";
		getJdbcTemplate().update(sql, id);
	}

	@Override
	public Store get(long id) {
		String sql = "SELECT Id, Name, Website FROM Stores WHERE Id = ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				id);
		return results.isEmpty() ? null : rowMapper(results.get(0));
	}
	
	@Override
	public StoreView getView(long id) {
		String sql = "SELECT * FROM StoreView WHERE StoreId = ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				id);
		return results.isEmpty() ? null : rowMapperView(results.get(0));
	}

	@Override
	public List<Store> getList() {
		String sql = "SELECT Id, Name, Website FROM Stores";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql);
		List<Store> stores = new ArrayList<Store>();
		for (Map<String, Object> map : results) {
			stores.add(rowMapper(map));
		}
		return stores;
	}

	private Store rowMapper(Map<String, Object> row) {
		Store store = new Store();
		store.setId((Long) row.get("Id"));
		store.setName((String) row.get("Name"));
		store.setWebsite((String) row.get("Website"));
		return store;
	}
	
	private StoreView rowMapperView(Map<String, Object> row) {
		StoreView store = new StoreView();
		store.setId((Long) row.get("StoreId"));
		store.setName((String) row.get("Name"));
		store.setWebsite((String) row.get("Website"));
		store.setImageUrl((String) row.get("ImageUrl"));
		store.setMenuQ((String) row.get("MenuQ"));
		store.setPaginatorQ((String) row.get("PaginatorQ"));
		return store;
	}
}
